﻿Imports System.Text

Namespace StatementSources

    Public Class CreateMatriculationStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeId As Integer, scheduleId As Integer, createdBy As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("INSERT [dbo].[Matriculations] ")
            stringBuilder.Append("([EmployeeId],[ScheduleId],[GradePoint],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm]) ")
            stringBuilder.Append("VALUES (@EmployeeId,@ScheduleId,@GradePoint,@CreatedBy,GETDATE(),@CreatedBy,GETDATE());")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeId", employeeId)
            Parameters.AddWithValue("@ScheduleId", scheduleId)
            Parameters.AddWithValue("@GradePoint", 0D)
            Parameters.AddWithValue("@CreatedBy", createdBy)
        End Sub
    End Class

    Public Class ReadAllMatriculationByScheduleIdStatementSource
        Inherits BaseStatementSource

        Public Sub New(scheduleId As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [EmployeeId],[ScheduleId],[GradePoint],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Matriculations] ")
            stringBuilder.Append("WHERE [ScheduleId]=@ScheduleId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@ScheduleId", scheduleId)
        End Sub

    End Class

    Public Class ReadAllMatriculationByEmployeeIdStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeId As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [EmployeeId],[ScheduleId],[GradePoint],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Matriculations] ")
            stringBuilder.Append("WHERE [EmployeeId]=@EmployeeId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeId", employeeId)
        End Sub
    End Class

    Public Class ReadMatriculationStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeId As Integer, scheduleId As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [EmployeeId],[ScheduleId],[GradePoint],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Matriculations] ")
            stringBuilder.Append("WHERE [ScheduleId]=@ScheduleId ")
            stringBuilder.Append("AND [EmployeeId]=@EmployeeId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeId", employeeId)
            Parameters.AddWithValue("@ScheduleId", scheduleId)
        End Sub
    End Class

    Public Class UpdateMatriculationStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeId As Integer, scheduleId As Integer, gradePointAverage As Decimal, modifiedBy As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("UPDATE [dbo].[Matriculations] SET ")
            stringBuilder.Append("[GradePoint]=ISNULL(@GradePoint,[GradePoint]),")
            stringBuilder.Append("[ModifiedBy]=@ModifiedBy,")
            stringBuilder.Append("[ModifiedDttm]=GETDATE() ")
            stringBuilder.Append("WHERE [EmployeeId]=@EmployeeId ")
            stringBuilder.Append("AND [ScheduleId]=@ScheduleId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeId", employeeId)
            Parameters.AddWithValue("@ScheduleId", scheduleId)
            Parameters.AddWithValue("@GradePoint", gradePointAverage)
            Parameters.AddWithValue("@ModifiedBy", modifiedBy)
        End Sub
    End Class

    Public Class DeleteMatriculationStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeId As Integer, scheduleId As Integer)
            Sql = "DELETE FROM [dbo].[Matriculations] WHERE [EmployeeId]=@EmployeeId AND [ScheduleId]=@ScheduleId;"

            Parameters.AddWithValue("@EmployeeId", employeeId)
            Parameters.AddWithValue("@ScheduleId", scheduleId)
        End Sub
    End Class

End Namespace

